﻿using BCCommon;
using BCCommon.TMS.MarketingCoupon;
using BCEntity.TMS.MarketingCoupon.MarketingCoupon;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.MarketingCoupon.MarketingCoupon
{
    public class MarketingCouponData : IMarketingCouponData
    {
        private const string SQL_QUERY = @"SELECT
		                                        A.MarketingCouponId,
		                                        A.MarketingCouponTypeId,
		                                        A.CouponType,
		                                        A.CouponName,
		                                        A.CouponSummark,
		                                        A.StartDate,
		                                        A.EndDate,
		                                        A.CouponAmount,
		                                        A.CouponCount,
		                                        A.AgentRecordId,
		                                        A.IsCommon,
		                                        A.CouponNumber,
		                                        A.Enabled,
		                                        A.CreateAdminId,
		                                        A.CreateAdminName,
		                                        A.CreateTime,
                                                A.IsReceive,
                                                A.IsUse,
                                                A.UserId,
                                                A.UserName,
                                                A.IsCommonBusiness,
                                                A.VehicleTransportBusinessConfigId,
                                                A.TransportBusiness,
                                                A.IsDeleted,
                                                A.ReceivedTime,
                                                A.UsingTime
                                            FROM
                                                MarketingCoupon A ";
        private readonly IDatabaseContext database;
        public MarketingCouponData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<MarketingCouponEntity> InsertAsync(MarketingCouponEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<MarketingCouponEntity> UpdateAsync(MarketingCouponEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<MarketingCouponEntity> GetSingle(long marketingCouponId)
        {
            var sql = typeof(MarketingCouponEntity).GetSelectSqlByPrimaryKeys();
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponEntity>(sql, new { marketingCouponId });
        }


        public async Task<MarketingCouponEntity> GetFirst(long marketingCouponTypeId)
        {
            var sql = $"{SQL_QUERY} WHERE A.MarketingCouponTypeId=?MarketingCouponTypeId ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponEntity>(sql, new { marketingCouponTypeId });
        }
        public async Task<IEnumerable<MarketingCouponEntity>> GetList(bool? isEnable)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add(" A.IsDeleted = 0 ");
            if (isEnable.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnable.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_QUERY} {whereSql} ORDER BY A.MarketingCouponId ";
            return await this.database.Slave.QueryAsync<MarketingCouponEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<MarketingCouponEntity>, int)> Query(long marketingCouponTypeId, bool? isReceive, bool? isUse,
            int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.MarketingCouponTypeId = ?MarketingCouponTypeId");
            parameters.Add("?MarketingCouponTypeId", marketingCouponTypeId, DbType.Int64);
            wheres.Add(" A.IsDeleted = 0 ");
            if (isReceive.HasValue)
            {
                wheres.Add("A.IsReceive = ?IsReceive");
                parameters.Add("?IsReceive", isReceive.Value, DbType.Boolean);
            }
            if (isUse.HasValue)
            {
                wheres.Add("A.IsUse = ?IsUse");
                parameters.Add("?IsUse", isUse.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sqlCount = " SELECT COUNT(A.MarketingCouponId) FROM marketingcoupon A ";
            string sql = $@"{SQL_QUERY} {whereSql} ORDER BY A.MarketingCouponId DESC LIMIT {(pageIndex - 1) * pageSize}, {pageSize};
                                {sqlCount}{whereSql} ";
            return await this.database.Slave.QueryPageableListAsync<MarketingCouponEntity>(sql, parameters);
        }

        public async Task<int> InsertAllAsync(IEnumerable<MarketingCouponEntity> entities)
        {
            return await this.database.Master.InsertAllAsync(entities, database.Transaction);
        }
        public async Task<int> UpdateEnabledByTypeId(long marketingCouponTypeId, bool enabled)
        {

            var sql = @" UPDATE marketingcoupon SET Enabled=?Enabled WHERE MarketingCouponTypeId=?MarketingCouponTypeId ";
            return await this.database.Master.ExecuteAsync(sql, new { marketingCouponTypeId, enabled }, database.Transaction);
        }

        public async Task<MarketingCouponEntity> GetRemainMarketingCoupon(long marketingCouponTypeId, long agentRecordId)
        {
            var sqlWhere = @" WHERE A.MarketingCouponTypeId=?MarketingCouponTypeId AND A.AgentRecordId=?AgentRecordId 
                       AND IsReceive=0 AND A.IsDeleted = 0 ";
            var sql = $" {SQL_QUERY} {sqlWhere} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponEntity>(sql, new { marketingCouponTypeId, agentRecordId });
        }

        public async Task<IEnumerable<MarketingCouponEntity>> GetCanUseList(long userId, long vehicleTransportBusinessConfigId,string countyCode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId, DbType.Int64);
            parameters.Add("?CountyCode", countyCode, DbType.Int64);
            var sql = $@" {SQL_QUERY}
                            INNER JOIN marketingcouponreceive B ON A.MarketingCouponId=B.MarketingCouponId
                            WHERE A.IsCommonBusiness=0 AND A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId 
                                AND A.StartDate<=CURDATE() AND A.EndDate>= CURDATE() AND B.CountyCode=?CountyCode
                                AND A.UserId=?UserId AND A.IsReceive=1 AND A.IsUse=0 AND A.IsDeleted = 0
                            UNION ALL
                         {SQL_QUERY}
                            INNER JOIN marketingcouponreceive B ON A.MarketingCouponId=B.MarketingCouponId
                            WHERE A.IsCommonBusiness=1 AND A.StartDate<=CURDATE() AND A.EndDate>= CURDATE() AND A.UserId=?UserId 
                            AND A.IsReceive=1 AND A.IsUse=0 AND B.CountyCode=?CountyCode AND A.IsDeleted = 0 ";
            return await this.database.Slave.QueryAsync<MarketingCouponEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<MarketingCouponEntity>, int)> GetMarketingCouponList(long userId, MarketingCouponStatusEnum? marketingCouponStatus, int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            wheres.Add("A.UserId = ?UserId");
            parameters.Add("?UserId", userId, DbType.Int64);
            if (marketingCouponStatus.HasValue)
            {
                if (marketingCouponStatus.Value == MarketingCouponStatusEnum.Used)
                {
                    wheres.Add("A.IsUse = 1 AND IsDeleted=0 ");
                }
                else if (marketingCouponStatus.Value == MarketingCouponStatusEnum.Unused)
                {
                    wheres.Add(" A.IsUse = 0 AND IsDeleted=0 ");
                }
                else if (marketingCouponStatus.Value == MarketingCouponStatusEnum.Expired)
                {
                    wheres.Add(" A.EndDate< CURDATE() AND IsDeleted=0 ");
                }
                else if (marketingCouponStatus.Value == MarketingCouponStatusEnum.Deleted)
                {
                    wheres.Add(" IsDeleted=1 ");
                }
            }
            else
            {
                wheres.Add(" A.IsDeleted = 0 ");
            }
            string sqlCount = " SELECT COUNT(A.MarketingCouponId) FROM marketingcoupon A ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $@"{SQL_QUERY} {whereSql} ORDER BY A.MarketingCouponId DESC LIMIT {(pageIndex - 1) * pageSize}, {pageSize};
                                {sqlCount}{whereSql} ";
            return await this.database.Slave.QueryPageableListAsync<MarketingCouponEntity>(sql, parameters);
        }
    }
}
